package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pojo.Customer;
import util.DBUtil;

public class CustomerDaoImpl implements CustomerDao {

	public int addCustomer(Customer c) {
		String sql="insert into customer(id,cname,sex,sal,birth,favs)values(seqc1.nextval,?,?,?,?,?)";
		Connection conn=null;
		PreparedStatement pst =null;
		StringBuilder sb= new StringBuilder();
		int m=0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, c.getCname());
			pst.setBoolean(2, c.isSex());
			pst.setDouble(3, c.getSal());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			if(c.getFavs()!=null){
				for(String s:c.getFavs()){
					sb.append(s).append("@");
				}
				pst.setString(5, sb.toString());
			}
			else pst.setString(5, null);
			m = pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		System.out.println(m);
		return m;
	}

	public int deleteCustomer(Integer id) {
		String sql="delete customer where id =?";
		Connection conn=null;
		PreparedStatement pst =null;
		int m=0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, id.intValue());
			m=pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		System.out.println(m);
		return m;
	}

	public List<Customer> findAll() {
		List<Customer> cl = new ArrayList<Customer> ();
		String sql="select * from customer";
		PreparedStatement pst =null;
		ResultSet rs = null;
		Connection conn = null;
		Customer c =null;
		int m =0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				c=new Customer();
				c.setCname(rs.getString("cname"));//String cname = rs.getString("cname");
				c.setId(rs.getInt("id"));//int id = rs.getInt("id");
				c.setSal(rs.getDouble("sal"));//double sal = rs.getDouble("sal");
				c.setSex(rs.getBoolean("sex"));//boolean sex = rs.getBoolean("sex");
				c.setBirth(new Date(rs.getDate("birth").getTime()));//Date birth = new Date(rs.getDate("birth").getTime());
				if(rs.getString("favs")!=null){
					String[] favs = rs.getString("favs").split("@");
					c.setFavs(favs);
				}
				else c.setFavs(null);
				//System.out.println(id+"\t"+cname+"\t"+sal+"\t"+sex+"\t"+birth);
				System.out.println(c.getId()+"  "+c.getCname()+"  "+c.isSex()+"  "+c.getSal()+"  "+c.getBirth());
				cl.add(c);
			}
			m=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, rs);
		}
		System.out.println(m);
		
		return cl;
	}

	public int updateCustomer(Customer c) {
		String sql="update customer set cname=?,sex=?,sal=?,birth=?,favs=? where id =?";
		PreparedStatement pst =null;
		ResultSet rs = null;
		Connection conn = null;
		StringBuilder sb= new StringBuilder();
		int m=0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, c.getCname());
			pst.setBoolean(2,c.isSex());
			pst.setDouble(3, c.getSal());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			if(c.getFavs()!=null){
				for(String s:c.getFavs()){
					sb.append(s).append("@");
				}
				pst.setString(5, sb.toString());
			}
			else pst.setString(5, null);
			pst.setInt(6, c.getId());
			m = pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		System.out.println(m);
		return m;
	}

	public Customer SelectCustomer(Integer id) {
		String sql="select * from customer where id = ?";
		PreparedStatement pst =null;
		ResultSet rs = null;
		Connection conn = null;
		Customer c = new Customer();
		String favsS= null;
		int m =0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs=pst.executeQuery();
			while(rs.next()){
				c.setCname(rs.getString("cname"));//String cname = rs.getString("cname");
				c.setId(rs.getInt("id"));//int id = rs.getInt("id");
				c.setSal(rs.getDouble("sal"));//double sal = rs.getDouble("sal");
				c.setSex(rs.getBoolean("sex"));//boolean sex = rs.getBoolean("sex");
				c.setBirth(new Date(rs.getDate("birth").getTime()));//Date birth = new Date(rs.getDate("birth").getTime());
				//System.out.println(id+"\t"+cname+"\t"+sal+"\t"+sex+"\t"+birth);
				favsS = rs.getString("favs");
				if(favsS==null){
					c.setFavs(null);
				}
				else{
					c.setFavs(favsS.split("@"));
				}
			}
			m=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, rs);
		}
		System.out.println(c.getId()+"  "+c.getCname()+"  "+c.isSex()+"  "+c.getSal()+"  "+c.getBirth());
		return c;
	}
}
